#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include "../../app/gconstant.h"
#include "util_sqlite.h"
/**
 * function:Init the db and table with the db and table name
 * *pucDBName:sqlname
 * *tableName:tablename
 * 返回值    　    0x0A    内存变量校验出错        0x00    成功        0x0B    数据库系统崩溃        0x04    输入错误        0x0C    数据库已经存在        0x0E    数据库已经打开，不能删除
 */
int SQ_DBCreate(char *pucDBName, char *tableName )
{
    sqlite3 *db = NULL;
    char sql[512];
    char *zErrMsg = NULL;
    int rc;
    printf("SQ_DBCreate--->>>\n");
    char fullname[100];
    strcpy(fullname, SQLITE_MENU);
    strcat(fullname, pucDBName);
    rc = sqlite3_open(fullname, &db); /* 打开数据库 */
    if (rc)
    {
        printf("open db %s error\n", pucDBName);
        sqlite3_close(db);
        return 0x0b;
    }
    else
    	printf("open db sucess\n");


    sprintf(sql, "CREATE TABLE %s( ID INTEGER PRIMARY KEY, WIFI_ID INTEGER, MAST_ID INTEGER, SSID VARCHAR(128),PASSWORD VARCHAR(128),MAC VARCHAR(128),LONGITUDE REAL, LATITUDE REAL,AREA_ID INTEGER);", tableName );
    rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);
    if (rc != SQLITE_OK)
    {
    	printf("creat db fail\n");
        printf("error = %s \n", zErrMsg);
        sqlite3_close(db);
        return 0x0c;
    }
    else
    {
    	printf("creat db success\n");
    	sqlite3_close(db);
    	printf("SQ_DBCreate<<<---\n");
        return 0;
    }
}

/**
 * function:Insert recodes to sql
 * *pucDBName:sqlname
 * *tableName:tablename
 * 返回值    　    0x0A    内存变量校验出错        0x00    成功        0x0B    数据库系统崩溃        0x04    输入错误        0x0C    数据库已经存在        0x0E    数据库已经打开，不能删除
 */
int SQ_DBInsert(char *pucDBName, char *tableName ,Wifilist *wifilist,int num)
{
sqlite3 *db = NULL;
    char sql[512];
    char *zErrMsg = NULL;
    int rc;
    int i,index;
    int j=0;
    int k=0;
    char **dbResult;
    int nRow = 0;
    int nColumn =0;
    printf("SQ_DBInsert--->>>\n");
    char fullname[100];
    strcpy(fullname, SQLITE_MENU);
    strcat(fullname, pucDBName);
    rc = sqlite3_open(fullname, &db); /* 打开数据库 */
    if (rc)
    {
        printf("open db %s error\n", pucDBName);
        sqlite3_close(db);
        return 0x0b;
    }
    else
        printf("open db sucess\n");
    for(i=0;i<num;i++)
    {
    	nRow = 0;
    	nColumn =0;
    	sprintf(sql, "SELECT * FROM %s WHERE MAC = '%s';", tableName, wifilist[i].wifi_mac);
    	rc = sqlite3_get_table( db, sql, &dbResult, &nRow, &nColumn, &zErrMsg);
    	if (rc == SQLITE_OK && nRow >0)
        {


                // 前两个字段为字段名 field0, field1, row[0][0], row[0][1], row[1][0], row[1][1] ... ... ....

                // 是一维数组,不是二维数组,反正记着第0,第1列的值为字段名,然后才是字段值;
    		    printf("--------------------------------------------------------------\n");
                printf( "The mac address has exist and the record will be update !\n");
                index = nColumn; //字段值从index开始呀
                for( j = 0; j < nRow ; j++ )

                {
                	  for( k = 0 ; k < nColumn; k++ )

                	 {

                	    printf( "%-5s ",dbResult[index++]);

                	  }
                	  printf( "\n \n");
   				sprintf(sql, "UPDATE %s SET WIFI_ID =%d,MAST_ID =%d,SSID ='%s',PASSWORD ='%s',LONGITUDE =%f,LATITUDE =%f,AREA_ID =%d WHERE MAC='%s';",tableName,wifilist[i].wifi_id,wifilist[i].wifi_matser_id,wifilist[i].wifi_ssid,wifilist[i].wifi_password,wifilist[i].wifi_longitude,wifilist[i].wifi_latitude,wifilist[i].wifi_area_id,wifilist[i].wifi_mac);
				rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);
				if (rc != SQLITE_OK)
    			{
      			printf("zErrMsg = %s \n", zErrMsg);
      			sqlite3_close(db);
       			return 0x0c;
  			 	}
				else
				{
				printf( "The record hsa been updated !\n");
			    printf("--------------------------------------------------------------\n");
				}
                }
        }
        else
        {
        printf("--------------------------------------------------------------\n");
        printf( "The mac address hasn't exist and the record will be insert !\n");
        sprintf(sql, "INSERT INTO %s (WIFI_ID,MAST_ID,SSID,PASSWORD,MAC,LONGITUDE,LATITUDE,AREA_ID) VALUES(%d,%d,'%s','%s','%s',%f,%f,%d);",tableName, wifilist[i].wifi_id,wifilist[i].wifi_matser_id,wifilist[i].wifi_ssid,wifilist[i].wifi_password,wifilist[i].wifi_mac,wifilist[i].wifi_longitude,wifilist[i].wifi_latitude,wifilist[i].wifi_area_id);
        rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);
        if (rc != SQLITE_OK)
    	{
      	  printf("zErrMsg = %s \n", zErrMsg);
      	  sqlite3_close(db);
       	 return 0x0c;
  		}
        else
        {
        printf( "The record hsa been inserted !\n");
        printf("--------------------------------------------------------------\n");
        }
        }
      sqlite3_free_table( dbResult );
    }
    printf("SQ_DBInsert<<<---\n");
    return 0;
}


/**
 * function:deleate recode from sql
 * *pucDBName:sqlname
 * *tableName:tablename
 * 返回值    　    0x0A    内存变量校验出错        0x00    成功        0x0B    数据库系统崩溃        0x04    输入错误        0x0C    数据库已经存在        0x0E    数据库已经打开，不能删除
 */
int SQ_DBDelete(char *pucDBName, char *tableName ,char *key,char *value)
{
    sqlite3 *db = NULL;
    char sql[512];
    char *zErrMsg = NULL;
    int rc;
    printf("SQ_DBDelete--->>>\n");
    char fullname[100];
    strcpy(fullname, SQLITE_MENU);
    strcat(fullname, pucDBName);
    rc = sqlite3_open(fullname, &db); /* 打开数据库 */
    if (rc)
    {
        printf("open db %s error\n", pucDBName);
        sqlite3_close(db);
        return 0x0b;
    }
    else
        printf("open db sucess\n");
    sprintf(sql, "DELETE FROM %s WHERE %s=%s;", key, value);
    rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);
    if (rc != SQLITE_OK)
    {
    	printf("delete recode fail\n");
        printf("zErrMsg = %s \n", zErrMsg);
        sqlite3_close(db);
        return 0x0c;
    }
    else
    {
    	sqlite3_close(db);
    	printf("delete recode success\n");
    }
    	printf("SQ_DBDelete<<<---\n");
    	return 0;
}

/**
 * function:update recode in sql
 * *pucDBName:sqlname
 * *tableName:tablename
 * 返回值    　    0x0A    内存变量校验出错        0x00    成功        0x0B    数据库系统崩溃        0x04    输入错误        0x0C    数据库已经存在        0x0E    数据库已经打开，不能删除
 */
int SQ_DBUpdate(char *pucDBName, char *tableName ,Wifilist wifilist)
{
    sqlite3 *db = NULL;
    char sql[512];
    char *zErrMsg = NULL;
    int rc;
    printf("SQ_DBUpdate--->>>\n");
    char fullname[100];
    strcpy(fullname, SQLITE_MENU);
    strcat(fullname, pucDBName);
    rc = sqlite3_open(fullname, &db); /* 打开数据库 */
    if (rc)
    {
        printf("open db %s error\n", pucDBName);
        sqlite3_close(db);
        return 0x0b;
    }
    else
    printf("open db sucess\n");
    sprintf(sql, "UPDATE %s SET WIFI_ID =%d,MAST_ID =%d,SSID ='%s',PASSWORD ='%s',LONGITUDE =%f,LATITUDE =%f,AREA_ID =%d WHERE MAC='%s';",tableName,wifilist.wifi_id,wifilist.wifi_matser_id,wifilist.wifi_ssid,wifilist.wifi_password,wifilist.wifi_longitude,wifilist.wifi_latitude,wifilist.wifi_area_id,wifilist.wifi_mac);
    rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);
    if (rc != SQLITE_OK)
    {
    	printf("update recode fail\n");
        printf("zErrMsg = %s \n", zErrMsg);
        sqlite3_close(db);
        return 0x0c;
    }
    else
    {
    	sqlite3_close(db);
    	printf("update recode success\n");
    }
    printf("SQ_DBUpdate<<<---\n");
    return 0;
}

/**
 * function:select recode in sql
 * *pucDBName:sqlname
 * *tableName:tablename
 * 返回值    　    0x0A    内存变量校验出错        0x00    成功        0x0B    数据库系统崩溃        0x04    输入错误        0x0C    数据库已经存在        0x0E    数据库已经打开，不能删除
 */
int SQ_DBSelect(char *pucDBName, char *tableName ,char *key,char *value,Wifilist_local *wifilist_local)
{
    sqlite3 *db = NULL;
    char sql[512];
    char *zErrMsg = NULL;
    char **dbResult;
	int nRow, nColumn;
    int rc ,i,j,index;
    printf("SQ_DBSelect--->>>\n");
    char fullname[100];
    strcpy(fullname, SQLITE_MENU);
    strcat(fullname, pucDBName);
    rc = sqlite3_open(fullname, &db); /* 打开数据库 */
    if (rc)
    {
        printf("open db %s error\n", pucDBName);
        sqlite3_close(db);
        return 0x0b;
    }
    else
    printf("open db sucess\n");
    sprintf(sql, "SELECT * FROM %s WHERE %s = '%s';",tableName, key, value);
    rc = sqlite3_get_table( db, sql, &dbResult, &nRow, &nColumn, &zErrMsg);
    if (rc == SQLITE_OK )
    {
         printf("The number of records is:%d\n", nRow);
         printf("--------------------------------\n");
         index = nColumn; //字段值从index开始呀
         for( i = 0; i < nRow ; i++ )
         {
        	printf("receive the %d recode \n", i);
            wifilist_local[i].id = atoi(dbResult[index++]);
        	wifilist_local[i].wifi_id = atoi(dbResult[index++]);
        	wifilist_local[i].wifi_matser_id = atoi(dbResult[index++]);
			stpcpy(wifilist_local[i].wifi_ssid, dbResult[index++]);
			stpcpy(wifilist_local[i].wifi_password, dbResult[index++]);
			stpcpy(wifilist_local[i].wifi_mac, dbResult[index++]);
			wifilist_local[i].wifi_longitude= atof(dbResult[index++]);
			wifilist_local[i].wifi_latitude = atof(dbResult[index++]);
        	wifilist_local[i].wifi_area_id = atoi(dbResult[index++]);
         }
    }
    else
    {
    	printf("select recode fail\n");
        printf("zErrMsg = %s \n", zErrMsg);
        sqlite3_close(db);
        return 0x0c;
    }
    sqlite3_free_table( dbResult );
    sqlite3_close(db);
    printf("SQ_DBSelect<<<---\n");
    return nRow;
}


